package zy.dao.base.type.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.type.TypeDAO;
import zy.entity.base.type.T_Base_Type;
@Repository
public class TypeDAOImpl extends BaseDaoImpl implements TypeDAO{

	@Override
	public List<T_Base_Type> list(Map<String, Object> param) {
		Object tp_name = param.get("tp_name");
		Object tp_upcode = param.get("tp_upcode");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select tp_id,tp_code,tp_name,tp_spell,tp_upcode,tp_rate,tp_salerate");
		sql.append(" from t_base_type t");
		sql.append(" where 1 = 1");
		if (tp_upcode != null && !"".equals(tp_upcode)) {
			sql.append(" and (t.tp_upcode = :tp_upcode or t.tp_code = :tp_upcode) ");
		}
        if(null != tp_name && !"".equals(tp_name)){
        	sql.append(" and (instr(t.tp_name,:tp_name)>0 or instr(t.tp_spell,:tp_name)>0)");
        }
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by :sidx :sord");
		List<T_Base_Type> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Type.class));
		return list;
	}

	@Override
	public Integer queryByName(T_Base_Type type) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select tp_id from T_Base_Type");
		sql.append(" where 1=1");
		if(null != type.getTp_name() && !"".equals(type.getTp_name())){
			sql.append(" and tp_name=:tp_name");
		}
		if(null != type.getTp_id() && type.getTp_id() > 0){
			sql.append(" and tp_id <> :tp_id");
		}
		sql.append(" and companyid=:companyid");
		sql.append(" limit 1");
		try{
			Integer id = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new BeanPropertySqlParameterSource(type),Integer.class);
			return id;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public void save(T_Base_Type type) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select f_three_code(max(tp_code+0)) from T_Base_Type ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(type), String.class);
		type.setTp_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO T_Base_Type");
		sql.append(" (tp_code,tp_name,tp_spell,tp_upcode,tp_rate,tp_salerate,companyid)");
		sql.append(" VALUES(:tp_code,:tp_name,:tp_spell,:tp_upcode,:tp_rate,:tp_salerate,:companyid)");
		
		KeyHolder holder = new GeneratedKeyHolder(); 
		
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(type),holder);
		int id = holder.getKey().intValue();
		type.setTp_id(id);
	}

	@Override
	public void del(Integer tp_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM T_Base_Type");
		sql.append(" WHERE tp_id=:tp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("tp_id", tp_id));
	}

	@Override
	public T_Base_Type queryByID(Integer tp_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select tp_id,tp_code,tp_name,tp_spell,tp_upcode,tp_rate,tp_salerate from T_Base_Type");
		sql.append(" where tp_id=:tp_id");
		sql.append(" limit 1");
		try{
			T_Base_Type data = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("tp_id", tp_id),new BeanPropertyRowMapper<>(T_Base_Type.class));
			return data;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public void update(T_Base_Type type) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" update T_Base_Type");
		sql.append(" set tp_name=:tp_name,tp_spell=:tp_spell,tp_rate=:tp_rate,tp_salerate=:tp_salerate");
		sql.append(" where tp_id=:tp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(type));
	}
	
}
